import pymysql


class DButils:

    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset

    def update(self, sql, data=None):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        try:
            with db.cursor() as cursor:
                # 单条更新用execute
                cursor.execute(sql, data)
                db.commit()
                print('数据操作成功！')
        except Exception as e:
            db.rollback()  # 新增回滚操作
            print('数据操作失败！', e)
        finally:
            db.close()

    def select(self, sql, data=None):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, data)
                result = cursor.fetchall()
                return result
        except Exception as e:
            print('数据查询失败！', e)
        finally:
            db.close()

    def add(self, sql, data=None):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, data)
                db.commit()
                print("添加数据成功!")
        except Exception as e:
            db.rollback()
            print("添加数据失败!", e)
        finally:
            db.close()


if __name__ == '__main__':
    db = DButils('localhost', 'root', '123456', 'hrsx')
    sql = 'select * from person'
    sql_insert = """
        insert into person (username, age, sex, high) 
        values (%s, %s, %s, %s)
    """

    data = []
    for i in range(10):
        s1 = input("请输入姓名:")
        s2 = int(input("请输入年龄:"))
        s3 = input("请输入性别:")
        s4 = "{:.2f}".format(float(input("请输入身高:")))
        data.extend([s1, s2, s3, s4])
        print(data)
        db.add(sql_insert, data)
        data.clear()
    # 查询并打印结果
    result = db.select(sql)
    print(result)
